package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.ExamResult;
import preHandle.PreHandle;

public class ExamResultDao {

	public static Integer addResult(ExamResult er) {
		String sql = "INSERT INTO examresult (exam_date,score,st_id,sb_id)"
				+ " VALUES(STR_TO_DATE(?,'%Y-%m-%d'),?,?,?)";
		try {
			return PreHandle.excuteUpdate(sql, er);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public static Integer uptResult(ExamResult er) {
		String sql = "UPDATE examresult set exam_date=?,score=?,sb_id=?,st_id=? WHERE id=?";
		try {
			return PreHandle.excuteUpdate(sql, er);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public static Integer delResult(int id) {
		String sql = "DELETE FROM examresult where id=?";
		try {
			return PreHandle.excuteUpdate(sql, id);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public static List<ExamResult> paging(int pageNo, int pageSize) {
		List<ExamResult> list = new ArrayList<ExamResult>();
		int start = (pageNo - 1) * pageSize;
		if (start < 0) {
			return null;
		}
		String sql = "SELECT * FROM examresult LIMIT " + start + "," + pageSize
				+ "";
		Connection con = PreHandle.getConnection();
		PreparedStatement pstmt = null;
		ResultSet res = null;
		try {
			pstmt = con.prepareStatement(sql);
			res = pstmt.executeQuery();
			while (res.next()) {
				list.add(new ExamResult(res.getString("exam_date"), res
						.getFloat("score"), res.getInt("st_id"), res
						.getInt("sb_id"), res.getInt("id")));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
